# -*- coding: utf-8 -*-
# __author__:HLZ
# 2020/9/16 15:30


import datetime
import re
import traceback

import MySQLdb
from dbutils.persistent_db import PersistentDB
from settings import config


class SqlUtil(object):

    def __init__(self, host=None, port=None, user=None, password=None, database=None, charset="utf8mb4"):
        mysql_info = config.MYSQL_CONNECT
        array_info = mysql_info.split("/")
        connect_info = re.split(pattern='[@:]', string=array_info[2])

        if host is None:
            host = connect_info[2]
        if port is None:
            port = int(connect_info[3])
        if user is None:
            user = connect_info[0]
        if password is None:
            password = connect_info[1]
        if database is None:
            database = array_info[3]

        self.pool = PersistentDB(MySQLdb, host=host, port=int(port), user=user, passwd=password,
                                 db=database, charset=charset)

    def _execute(self, sql, values: list):
        with self.pool.connection() as db:
            with db.cursor() as cursor:
                effect_row = cursor.execute(sql, values)
                try:
                    db.commit()
                except Exception as ex:
                    traceback.print_exc()
                    db.rollback()
                return effect_row

    # def _query(self):
    #     """返回查询结果"""
    #     pass

    """
    :param table:
    :param insert_data  type:[{},{}]:
    :return:effect_row 1 影响的行数
    """

    def insert(self, table, insert_data, replace=False):
        with self.pool.connection() as db:
            with db.cursor() as cursor:

                effect_row = ""
                insert_type = "INSERT"
                if replace:
                    insert_type = "REPLACE"
                for idx, data in enumerate(insert_data, start=1):
                    sql = "{insert_type} INTO {table} (".format(table=table, insert_type=insert_type) + \
                          ",".join([f'`{_}`' for _ in data.keys()]) + \
                          ") values(" + \
                          ",".join(["%s" for _ in range(len(data.values()))]) + \
                          ")"
                    effect_row = cursor.execute(sql, list(data.values()))
                    # 每一千条提交一次
                    if idx % 1000 == 0 or idx == len(insert_data):
                        try:
                            db.commit()
                        except Exception:
                            db.rollback()
                            print('【ERROR】 事务提交失败')
                        print('commit:', idx)
                return effect_row

    """
    :param table:
    :param data type 字典 {}:
    :param condition tpye 字典 {}:
    :return:
    """

    def update(self, table, data, condition=None):
        with self.pool.connection() as db:
            with db.cursor() as cursor:

                update_list = self._deal_values(data)
                update_data = ",".join(update_list)
                if condition is not None:
                    condition_list = self._deal_values(condition)
                    condition_data = ' and '.join(condition_list)
                    sql = "update {table} set {values} where {condition}".format(table=table, values=update_data,
                                                                                 condition=condition_data)
                else:
                    sql = "update {table} set {values}".format(table=table, values=update_data)
                effect_row = cursor.execute(sql)
                db.commit()
                # self.close_db()
                return effect_row

    def select_some(self, table, condition=None, as_dict=False):
        with self.pool.connection() as db:
            if as_dict:
                cursor = db.cursor(MySQLdb.cursors.DictCursor)
            else:
                cursor = db.cursor()

        if condition is not None:
            condition_list = self._deal_values(condition)
            condition_data = ' and '.join(condition_list)
        sql = "select * from {table} where {condition}".format(table=table,
                                                               condition=condition_data)

        cursor.execute(sql)
        result = cursor.fetchall()
        cursor.close()
        if result:
            return result
        else:
            return None

    def query_sql(self, sql, values=None, as_dict=False):
        """基础查询方法"""
        result = None
        with self.pool.connection() as db:
            if as_dict:
                cursor = db.cursor(MySQLdb.cursors.DictCursor)
            else:
                cursor = db.cursor()

            try:
                if not isinstance(values, list) and values is not None:
                    values = [values]

                cursor.execute(sql, values)
                result = cursor.fetchall()
            except Exception as ex:
                print(ex)
            finally:
                cursor.close()
        return result

    def _deal_values(self, value):
        # 如果是字符串则加上''
        if isinstance(value, str):
            value = ("'{value}'".format(value=value))
        # 如果是字典则变成key=value形式
        elif isinstance(value, dict):
            result = []
            for key, value in value.items():
                value = self._deal_values(value)
                res = "{key}={value}".format(key=key, value=value)
                result.append(res)
            return result
        elif isinstance(value, datetime.datetime):
            value = ("'{value}'".format(value=str(value)))
        elif value is None:
            value = 'null'
        else:
            value = (str(value))
        return value
